/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package project;

import java.sql.Date;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/**
 *
 * @author jon
 */
public class MenuDriver
{
    private BufferedReader scan = new BufferedReader(new InputStreamReader(System.in));
    public MenuDriver()
    {
    }

    public void run()
    {
        boolean done = false;
        while (done == false)
        {
            System.out.println("Please input your choice");
            System.out.println("1: Insert\n2: Update\n3: Delete\n");
            int input;
            int choice;
            try
            {
                input = Integer.parseInt(scan.readLine());
                switch(input)
                {
                    case 1:
                        System.out.println("What would you like to insert?");
                        System.out.println("1. Branch\n2. Employee\n3. Properties\n4. Owner\n5. Client");
                        choice = Integer.parseInt(scan.readLine());
                        switch(choice)
                        {
                            case 1:
                                System.out.println("Insert in Branch\nzipcode, state, city, street, telephone number, fax number," +
                                        "manager name, time started, manager bonus");
                                int zipcode = Integer.parseInt(scan.readLine());
                                String state = scan.readLine();
                                String city = scan.readLine();
                                String street = scan.readLine();
                                String telephone_number = scan.readLine();
                                String fax_number = scan.readLine();
                                String manager_name = scan.readLine();
                                Date time_started = Date.valueOf(scan.readLine());
                                double manager_bonus = 0;

                                Branch newBranch = new Branch(0, zipcode, state, city, street, telephone_number, fax_number, manager_name, time_started, manager_bonus);
                                newBranch.insert();
                                break;
                            case 2:
                                System.out.println("Insert in Employees\nbranch, zipcode, state, city, street, name, salary, position, dob, gender");
                                int emp_branch_number = Integer.parseInt(scan.readLine());
                                int emp_zipcode = Integer.parseInt(scan.readLine());
                                String emp_state = scan.readLine();
                                String emp_city = scan.readLine();
                                String emp_street = scan.readLine();
                                String emp_name = scan.readLine();
                                double emp_salary = Double.parseDouble(scan.readLine());
                                String emp_position = scan.readLine();
                                Date emp_dob = Date.valueOf(scan.readLine());
                                String emp_gender = scan.readLine();
                                int manager_number = Integer.parseInt(scan.readLine());

                                Employee newEmployee = new Employee(emp_branch_number, emp_zipcode, emp_state, emp_city,
                                        emp_street, emp_name, emp_salary, emp_position, emp_dob, emp_gender, manager_number);
                                newEmployee.insert();
                                break;
                            case 3:
                                System.out.println("Insert in Properties");
                                int employee_number = Integer.parseInt(scan.readLine());
                                String property_owner = scan.readLine();
                                String property_type = scan.readLine();
                                double monthly_management_fee = Double.parseDouble(scan.readLine());
                                double maximum_rent = Double.parseDouble(scan.readLine());
                                double minimum_rent = Double.parseDouble(scan.readLine());
                                int num_bedrooms = Integer.parseInt(scan.readLine());
                                int num_bathrooms = Integer.parseInt(scan.readLine());
                                Date date_last_asking_rent = Date.valueOf(scan.readLine());
                                int square_feet = Integer.parseInt(scan.readLine());
                                int property_zipcode = Integer.parseInt(scan.readLine());
                                String property_state = scan.readLine();
                                String property_city = scan.readLine();
                                String property_street = scan.readLine();

                                Property newProperty = new Property(0, employee_number, 0, property_owner, property_type,
                                        monthly_management_fee, maximum_rent, minimum_rent, num_bedrooms, num_bathrooms, 0, date_last_asking_rent,
                                        square_feet, property_zipcode, property_state, property_city, property_street);

                                newProperty.insert();
                                break;
                            case 4:
                                System.out.println("Insert in Owner");
                                String owner_Name = scan.readLine();
                                String owner_telephone_number = scan.readLine();
                                String owner_fax_number = scan.readLine();
                                int owner_type = Integer.parseInt(scan.readLine());
                                if (owner_type == 0)
                                {
                                    Owner newOwner = new Owner(owner_Name, owner_telephone_number, owner_fax_number, owner_type, "", "");
                                    newOwner.insert();
                                }
                                else if (owner_type == 1)
                                {
                                    String business_type = scan.readLine();
                                    String contact_name = scan.readLine();
                                    Owner newBusinessOwner = new Owner(owner_Name, owner_telephone_number, owner_fax_number, owner_type, business_type, contact_name);
                                    newBusinessOwner.insert();
                                }         
                                break;
                            case 5:
                                System.out.println("Insert in Client");
                                String client_name = scan.readLine();
                                String associate_name = scan.readLine();
                                Date client_date = Date.valueOf(scan.readLine());
                                double max_rent = Double.parseDouble(scan.readLine());
                                String preferred_type = scan.readLine();
                                int branch_id = Integer.parseInt(scan.readLine());
                                Client newClient = new Client(0, client_name, associate_name, client_date, max_rent, preferred_type, branch_id);
                                newClient.insert();
                                break;
                            case 6:
                                System.out.println("Insert in Lease");
                                int lease_property_number = Integer.parseInt(scan.readLine());
                                String lease_associate_name = scan.readLine();
                                int client_zip = Integer.parseInt(scan.readLine());
                                String client_state = scan.readLine();
                                String client_city = scan.readLine();
                                String client_street = scan.readLine();
                                double deposit_amount = Double.parseDouble(scan.readLine());
                                Date start_date = Date.valueOf(scan.readLine());
                                Date end_date = Date.valueOf(scan.readLine());
                                int duration = Integer.parseInt(scan.readLine());

                                Lease newLease = new Lease(0, lease_property_number, lease_associate_name,
                                        client_zip, client_state, client_city, client_street, deposit_amount, start_date,
                                        end_date, duration);
                                newLease.insert();

                                break;
                            case 7:
                                System.out.println("Insert in Newspaper");
                                String newspaper_name = scan.readLine();
                                int newspaper_zipcode = Integer.parseInt(scan.readLine());
                                String newspaper_state = scan.readLine();
                                String newspaper_city = scan.readLine();
                                String newspaper_street = scan.readLine();
                                double cost_of = Double.parseDouble(scan.readLine());
                                Date date_of = Date.valueOf(scan.readLine());
                                int property_id_fk = Integer.parseInt(scan.readLine());

                                Newspaper newNewspaper = new Newspaper(newspaper_name, newspaper_zipcode, newspaper_state, newspaper_city, newspaper_street,
                                        cost_of, date_of, property_id_fk);
                                newNewspaper.insert();
                                break;
                            case 8:
                                System.out.println("Insert in AgreeTo");
                                int agree_client_number = Integer.parseInt(scan.readLine());
                                int agree_lease_number = Integer.parseInt(scan.readLine());

                                AgreeTo newAgreement = new AgreeTo(agree_client_number, agree_lease_number);

                                newAgreement.insert();

                                break;
                            case 9:
                                System.out.println("Insert in Owns");
                                int owns_property_number = Integer.parseInt(scan.readLine());
                                String owns_owner_name = scan.readLine();

                                Owns newOwns = new Owns(owns_property_number, owns_owner_name);
                                newOwns.insert();
                                break;
                            case 10:
                                System.out.println("Insert in Views");
                                int views_client_number = Integer.parseInt(scan.readLine());
                                int views_property_number = Integer.parseInt(scan.readLine());
                                String date_of_view = scan.readLine();
                                String comments = scan.readLine();

                                Views newViews = new Views(views_client_number, views_property_number,
                                        date_of_view, comments);
                                newViews.insert();
                                break;
                            default:
                                System.out.println("Cancelling");
                                break;
                        }
                        break;
                    case 2:
                        System.out.println("What would you like to update?");
                        choice = Integer.parseInt(scan.readLine());
                        switch(choice)
                        {
                            case 1:
                                System.out.println("Update in Branch");
                                break;
                            case 2:
                                System.out.println("Update in Employees");
                                break;
                            case 3:
                                System.out.println("Update in Properties");
                                break;
                            case 4:
                                System.out.println("Update in Owner");
                                break;
                            case 5:
                                System.out.println("Update in Client");
                                break;
                            case 6:
                                System.out.println("Update in Lease");
                                break;
                            case 7:
                                System.out.println("Update in Newspaper");
                                break;
                            default:
                                System.out.println("Cancelling");
                                break;
                        }
                        break;
                    case 3:
                        System.out.println("What would you like to delete?");
                        choice = Integer.parseInt(scan.readLine());
                        switch(choice)
                        {
                            case 1:
                                System.out.println("Delete in Branch");
                                break;
                            case 2:
                                System.out.println("Delete in Employees");
                                break;
                            case 3:
                                System.out.println("Delete in Properties");
                                break;
                            case 4:
                                System.out.println("Delete in Owner");
                                break;
                            case 5:
                                System.out.println("Delete in Client");
                                break;
                            case 6:
                                System.out.println("Delete in Lease");
                                break;
                            case 7:
                                System.out.println("Delete in Newspaper");
                                break;
                            default:
                                System.out.println("Cancelling");
                                break;
                        }
                        break;
                    case 4:
                        System.out.println("Printing Branch information");
                        String city = scan.readLine();
                        BranchDetails(city);

                    case 0:
                        System.out.println("Quitting\n");
                        done = true;
                    default:
                        System.out.println("Invalid choice please try again.");
                }
            }
            catch (Exception e)
            {
                System.out.println(e.getMessage());
            }
        }
    }
    private void BranchDetails(String city)
    {
        try
        {
            String host = "jdbc:oracle:thin:@localhost:1521:xe";
            String username = "jon";
            String password = "chaosecco";
            String driverName = "oracle.jdbc.driver.OracleDriver";
            Class.forName(driverName);

            Connection connection = DriverManager.getConnection(host,
                    username, password);
            System.out.print("Connected successfully.");
            
            String selectQuery = "select * from branch where branch_address_city = ?";
            PreparedStatement ps = connection.prepareStatement(selectQuery);
            ps.setString(1, city);
            ResultSet result = ps.executeQuery();
            
            ResultSetMetaData metaData = result.getMetaData();
            int columns = metaData.getColumnCount();

            while (result.next())
            {
                for (int i = 1; i <= columns; i++)
                {
                    System.out.println(result.getString(i));
                }
            }
            connection.close();
        }
        catch (SQLException e)
        {
            System.out.println("ERROR: " + e.getMessage());
        }

        catch(ClassNotFoundException e)
        {
            System.out.println("ERROR: " + e.getMessage());
        }
    }
}
